﻿Imports System.Data.SqlClient

Public Class Lesson
    Inherits System.Web.UI.Page

    'Open the connection to the database
    'Dim connectionString As String = "Data Source=(localdb)\Projects;Initial Catalog=CIS441WinterParkWeb;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False"
    Dim connectionString As String = "Data Source=SHANK-PC\SQLEXPRESS;Initial Catalog=WinterPark;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False"
    Dim connection As New SqlConnection(connectionString)
    Dim com As SqlCommand
    Dim str As String

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            loadCombos()
        End If
    End Sub

    Protected Sub loadCombos()
        'clearing controls
        CustomerNameDropdownList.Items.Clear()

        connection.Open()
        'load customers
        str = " select '#Choose A Customer#' as CustomerName,-1 as CustomerID   union  select Surname + ', ' + FirstName as CustmerName,CustomerId  from CustomerTable order by CustomerName "
        com = New SqlCommand(str, connection)
        Dim Reader As SqlDataReader = com.ExecuteReader
        While reader.Read
            Dim lstItem As New ListItem(reader("CustomerName"), reader("CustomerId"))
            CustomerNameDropdownList.Items.Add(lstItem)
        End While
        reader.Close()

        connection.Close()
    End Sub
End Class